﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
namespace DuJianliang.ApplicationPlatform.Proj5715.Process
{
    public partial class ProcessWastage : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (Request.QueryString["DispatchCode"] != null && Request.QueryString["MissileCode"]!=null)
                {

                    string DispatchCode = Request.QueryString["DispatchCode"].Trim();
                    string MissileCode = Request.QueryString["MissileCode"].Trim();

                    string sql = string.Empty;

                    Database database = DatabaseFactory.CreateDatabase(Common.Helper.Instance.ConnectionStringName_SBODB);
                    DbCommand dbCommand = null;
                    if (Request.QueryString["ProcessSortNumber"] != null)
                    {
                        //某个工序
                        string ProcessSortNumber = Request.QueryString["ProcessSortNumber"].Trim();
                        sql = @"SELECT T0.ItemCode,T1.ItemName,T1.InvntryUom,T0.DistNumber,T0.LotNumber,SUM(T0.Quantity) AS Quantity                                FROM                                (								SELECT T1.ITEMCODE,-T3.QUANTITY QUANTITY,T4.DistNumber,T4.LotNumber                                FROM OIGE T0                                JOIN IGE1 T1 ON T0.DOCENTRY=T1.DOCENTRY                                JOIN OITL T2 ON T2.DocType=T0.ObjType AND T0.DocEntry=T2.DocEntry AND T1.LineNum=T2.DocLine AND T2.ManagedBy='10000045'                                JOIN ITL1 T3 ON T3.LogEntry=T2.LogEntry                                JOIN OSRN T4 ON T4.ItemCode=T3.ItemCode AND T4.SysNumber=T3.SysNumber                                WHERE T0.U_TRSNAME=201 AND T1.U_DISPATCHID=@U_SRCNUM  AND T1.U_MISSILECODE=@U_Missile AND T1.U_PROCESS=@U_Operation                                UNION ALL                                SELECT T1.ITEMCODE,T3.QUANTITY,T4.DistNumber,T4.LotNumber                                FROM OIGN T0                                JOIN IGN1 T1 ON T0.DOCENTRY=T1.DOCENTRY                                JOIN OITL T2 ON T2.DocType=T0.ObjType AND T0.DocEntry=T2.DocEntry AND T1.LineNum=T2.DocLine AND T2.ManagedBy='10000045'                                JOIN ITL1 T3 ON T3.LogEntry=T2.LogEntry                                JOIN OSRN T4 ON T4.ItemCode=T3.ItemCode AND T4.SysNumber=T3.SysNumber                                WHERE T0.U_TRSNAME=201 AND T1.U_DISPATCHID=@U_SRCNUM  AND T1.U_MISSILECODE=@U_Missile AND T1.U_PROCESS=@U_Operation                                                                UNION ALL								SELECT T1.ITEMCODE,-T3.QUANTITY QUANTITY,'',T4.DistNumber                                FROM OIGE T0                                JOIN IGE1 T1 ON T0.DOCENTRY=T1.DOCENTRY                                JOIN OITL T2 ON T2.DocType=T0.ObjType AND T0.DocEntry=T2.DocEntry AND T1.LineNum=T2.DocLine AND T2.ManagedBy='10000044'                                JOIN ITL1 T3 ON T3.LogEntry=T2.LogEntry                                JOIN OBTN T4 ON T4.ItemCode=T3.ItemCode AND T4.SysNumber=T3.SysNumber                                WHERE T0.U_TRSNAME=201 AND T1.U_DISPATCHID=@U_SRCNUM  AND T1.U_MISSILECODE=@U_Missile AND T1.U_PROCESS=@U_Operation                                                                UNION ALL                                  SELECT T1.ITEMCODE,T3.QUANTITY,'',T4.DistNumber                                FROM OIGN T0                                JOIN IGN1 T1 ON T0.DOCENTRY=T1.DOCENTRY                                JOIN OITL T2 ON T2.DocType=T0.ObjType AND T0.DocEntry=T2.DocEntry AND T1.LineNum=T2.DocLine AND T2.ManagedBy='10000044'                                JOIN ITL1 T3 ON T3.LogEntry=T2.LogEntry                                JOIN OBTN T4 ON T4.ItemCode=T3.ItemCode AND T4.SysNumber=T3.SysNumber                                WHERE T0.U_TRSNAME=201 AND T1.U_DISPATCHID=@U_SRCNUM  AND T1.U_MISSILECODE=@U_Missile AND T1.U_PROCESS=@U_Operation                                                                 UNION ALL                                                                SELECT T1.ITEMCODE,T1.QUANTITY,'',''                                FROM OIGE T0                                JOIN IGE1 T1 ON T0.DOCENTRY=T1.DOCENTRY                                JOIN OITM T2 ON T2.ITEMCODE=T1.ITEMCODE                                WHERE T2.MANBTCHNUM='N' AND T2.MANSERNUM='N' AND T0.U_TRSNAME=201 AND T1.U_DISPATCHID=@U_SRCNUM  AND T1.U_MISSILECODE=@U_Missile AND T1.U_PROCESS=@U_Operation                                                                UNION ALL                                                                SELECT T1.ITEMCODE,-T1.QUANTITY,'',''                                FROM OIGN T0                                JOIN IGN1 T1 ON T0.DOCENTRY=T1.DOCENTRY                                JOIN OITM T2 ON T2.ITEMCODE=T1.ITEMCODE                                WHERE T2.MANBTCHNUM='N' AND T2.MANSERNUM='N' AND T0.U_TRSNAME=201 AND T1.U_DISPATCHID=@U_SRCNUM  AND T1.U_MISSILECODE=@U_Missile AND T1.U_PROCESS=@U_Operation                                                                                ) T0                        JOIN OITM T1 ON T0.ItemCode=T1.ItemCode                        GROUP BY T0.ItemCode,T1.ItemName,T1.InvntryUom,T0.DistNumber,T0.LotNumber                        HAVING SUM(T0.QUANTITY)<>0";
                        dbCommand = database.GetSqlStringCommand(sql);
                        database.AddInParameter(dbCommand, "@U_SRCNUM", DbType.String, DispatchCode);
                        database.AddInParameter(dbCommand, "@U_Missile", DbType.String, MissileCode);
                        database.AddInParameter(dbCommand, "@U_Operation", DbType.String, ProcessSortNumber);
                    }
                    else
                    { 
                        //整个过程

                        sql = @"SELECT T0.ItemCode,T1.ItemName,T1.InvntryUom,T0.DistNumber,T0.LotNumber,SUM(T0.Quantity) AS Quantity                                FROM                                (								SELECT T1.ITEMCODE,-T3.QUANTITY QUANTITY,T4.DistNumber,T4.LotNumber                                FROM OIGE T0                                JOIN IGE1 T1 ON T0.DOCENTRY=T1.DOCENTRY                                JOIN OITL T2 ON T2.DocType=T0.ObjType AND T0.DocEntry=T2.DocEntry AND T1.LineNum=T2.DocLine AND T2.ManagedBy='10000045'                                JOIN ITL1 T3 ON T3.LogEntry=T2.LogEntry                                JOIN OSRN T4 ON T4.ItemCode=T3.ItemCode AND T4.SysNumber=T3.SysNumber                                WHERE T0.U_TRSNAME=201 AND T1.U_DISPATCHID=@U_SRCNUM  AND T1.U_MISSILECODE=@U_Missile                                UNION ALL                                SELECT T1.ITEMCODE,T3.QUANTITY,T4.DistNumber,T4.LotNumber                                FROM OIGN T0                                JOIN IGN1 T1 ON T0.DOCENTRY=T1.DOCENTRY                                JOIN OITL T2 ON T2.DocType=T0.ObjType AND T0.DocEntry=T2.DocEntry AND T1.LineNum=T2.DocLine AND T2.ManagedBy='10000045'                                JOIN ITL1 T3 ON T3.LogEntry=T2.LogEntry                                JOIN OSRN T4 ON T4.ItemCode=T3.ItemCode AND T4.SysNumber=T3.SysNumber                                WHERE T0.U_TRSNAME=201 AND T1.U_DISPATCHID=@U_SRCNUM  AND T1.U_MISSILECODE=@U_Missile                                                                UNION ALL								SELECT T1.ITEMCODE,-T3.QUANTITY QUANTITY,'',T4.DistNumber                                FROM OIGE T0                                JOIN IGE1 T1 ON T0.DOCENTRY=T1.DOCENTRY                                JOIN OITL T2 ON T2.DocType=T0.ObjType AND T0.DocEntry=T2.DocEntry AND T1.LineNum=T2.DocLine AND T2.ManagedBy='10000044'                                JOIN ITL1 T3 ON T3.LogEntry=T2.LogEntry                                JOIN OBTN T4 ON T4.ItemCode=T3.ItemCode AND T4.SysNumber=T3.SysNumber                                WHERE T0.U_TRSNAME=201 AND T1.U_DISPATCHID=@U_SRCNUM  AND T1.U_MISSILECODE=@U_Missile                                                                UNION ALL                                  SELECT T1.ITEMCODE,T3.QUANTITY,'',T4.DistNumber                                FROM OIGN T0                                JOIN IGN1 T1 ON T0.DOCENTRY=T1.DOCENTRY                                JOIN OITL T2 ON T2.DocType=T0.ObjType AND T0.DocEntry=T2.DocEntry AND T1.LineNum=T2.DocLine AND T2.ManagedBy='10000044'                                JOIN ITL1 T3 ON T3.LogEntry=T2.LogEntry                                JOIN OBTN T4 ON T4.ItemCode=T3.ItemCode AND T4.SysNumber=T3.SysNumber                                WHERE T0.U_TRSNAME=201 AND T1.U_DISPATCHID=@U_SRCNUM  AND T1.U_MISSILECODE=@U_Missile                                                                UNION ALL                                                                SELECT T1.ITEMCODE,T1.QUANTITY,'',''                                FROM OIGE T0                                JOIN IGE1 T1 ON T0.DOCENTRY=T1.DOCENTRY                                JOIN OITM T2 ON T2.ITEMCODE=T1.ITEMCODE                                WHERE T2.MANBTCHNUM='N' AND T2.MANSERNUM='N' AND T0.U_TRSNAME=201 AND T1.U_DISPATCHID=@U_SRCNUM  AND T1.U_MISSILECODE=@U_Missile                                                                UNION ALL                                                                SELECT T1.ITEMCODE,-T1.QUANTITY,'',''                                FROM OIGN T0                                JOIN IGN1 T1 ON T0.DOCENTRY=T1.DOCENTRY                                JOIN OITM T2 ON T2.ITEMCODE=T1.ITEMCODE                                WHERE T2.MANBTCHNUM='N' AND T2.MANSERNUM='N' AND T0.U_TRSNAME=201 AND T1.U_DISPATCHID=@U_SRCNUM  AND T1.U_MISSILECODE=@U_Missile                                                                                ) T0                        JOIN OITM T1 ON T0.ItemCode=T1.ItemCode                        GROUP BY T0.ItemCode,T1.ItemName,T1.InvntryUom,T0.DistNumber,T0.LotNumber                        HAVING SUM(T0.QUANTITY)<>0";
                        dbCommand = database.GetSqlStringCommand(sql);
                        database.AddInParameter(dbCommand, "@U_SRCNUM", DbType.String, DispatchCode);
                        database.AddInParameter(dbCommand, "@U_Missile", DbType.String, MissileCode);
                    }
                    
                    DataSet ds = database.ExecuteDataSet(dbCommand);

                    grv.DataSource = ds;
                    grv.DataBind();
                }
            }
        }
    }
}